Senior .NET Study Portal

IQueryable

IQueryable — Expression Trees for Remote Queries

"Use IQueryable when queries should be translated and executed remotely (databases, APIs), not in memory."

❌ Bad example:

public IEnumerable<Trade> GetTradesForSymbol(string symbol)
{
    return _dbContext.Trades.ToList() // loads ALL trades into memory
                            .Where(t => t.Symbol == symbol); // filters in memory
}

// Caller
var trades = repository.GetTradesForSymbol("AAPL"); // loads millions of rows

Using IEnumerable forces full materialization before filtering—catastrophic for large datasets.

✅ Good example:

public IQueryable<Trade> GetTradesForSymbol(string symbol)
{
    return _dbContext.Trades.Where(t => t.Symbol == symbol); // builds expression tree
}

// Caller
var trades = repository.GetTradesForSymbol("AAPL")
                       .OrderByDescending(t => t.Timestamp)
                       .Take(100); // SQL: SELECT TOP 100 ... WHERE Symbol = 'AAPL' ORDER BY ...

👉 IQueryable builds expression trees; EF Core translates to SQL and executes on database.

🔥 Composing queries before execution:

public IQueryable<Order> GetOrders()
{
    return _dbContext.Orders;
}

// Caller composes query further
var recentLargeOrders = orderService.GetOrders()
    .Where(o => o.Amount > 10000)
    .Where(o => o.CreatedAt > DateTime.UtcNow.AddDays(-7))
    .OrderByDescending(o => o.Amount); // still no execution

var results = recentLargeOrders.ToList(); // NOW executes as single SQL query

👉 Query composition is deferred until materialization (.ToList(), .First(), foreach).

🔥 Avoiding N+1 queries:

// ❌ Bad: N+1 problem
public IEnumerable<Order> GetOrdersWithCustomers()
{
    var orders = _dbContext.Orders.ToList(); // 1 query
    foreach (var order in orders)
    {
        var customer = order.Customer; // N queries (lazy loading)
    }
    return orders;
}

// ✅ Good: single query with join
public IQueryable<Order> GetOrdersWithCustomers()
{
    return _dbContext.Orders.Include(o => o.Customer); // SQL JOIN
}

👉 IQueryable enables Include() for eager loading, avoiding N+1 queries.

💡 In trading systems:

• Use IQueryable for database repositories to push filtering/sorting to SQL.
• Enable dynamic query composition for flexible reporting without loading everything.
• Avoid materializing with .ToList() prematurely—keep query deferred until final shape is known.

---

Questions & Answers

Q: What's the difference between IEnumerable and IQueryable?

A: IEnumerable executes in-memory (LINQ-to-Objects), IQueryable builds expression trees for remote execution (LINQ-to-SQL, LINQ-to-Entities). Use IQueryable for databases.

Q: When should I return IQueryable vs IEnumerable?

A: Return IQueryable from repository methods so callers can compose queries before execution. Return IEnumerable when data is already in memory or query composition isn't needed.

Q: Can IQueryable be enumerated multiple times?

A: Yes, but each enumeration re-executes the query against the database. Cache results with .ToList() if multiple enumerations are needed.

Q: What happens if I use unsupported operations in IQueryable?

A: Providers throw runtime exceptions if they can't translate operations to SQL (e.g., calling custom C# methods). Use .AsEnumerable() to switch to in-memory for unsupported logic.

Q: How do I switch from IQueryable to IEnumerable?

A: Call .AsEnumerable(). This forces remaining operations to execute in-memory via LINQ-to-Objects. Useful for operations EF can't translate.

Q: What's the performance cost of IQueryable?

A: Building expression trees has overhead, but it's negligible compared to database I/O. The benefit is pushing work to the database, reducing memory and network costs.

Q: Can IQueryable be used outside EF Core?

A: Yes. Any provider implementing IQueryProvider can expose IQueryable (e.g., LINQ-to-MongoDB, OData). Custom providers require implementing expression visitors.

Q: How does IQueryable relate to SQL injection?

A: Parameterized queries are generated by providers like EF Core, preventing SQL injection. Never concatenate strings into IQueryable predicates—use variables instead.

Q: Should I expose IQueryable from repositories?

A: Controversial. Pros: flexible querying. Cons: leaks data access concerns to callers. Consider returning IQueryable for read-only queries, but encapsulate writes.

Q: What's deferred execution in IQueryable?

A: Query construction doesn't execute; only enumeration (.ToList(), .First(), foreach) triggers execution. This enables composing filters/projections cheaply before hitting the database.